package cn.com.callback

import cn.com.kh.KHUtil
import com.alibaba.fastjson.JSONArray
import com.alibaba.fastjson.JSONObject
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import cn.com.callback.ServiceBackInvoke
import cn.com.elite.EliteBean;
import cn.com.elite.Util;
import java.text.SimpleDateFormat;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import cn.com.kh.getProperty;
/**
 *
 * @Title: aipHSTServiceTaskDeskList
 * @Description:华盛证券  任务台 查询 数据返回 涉及动态sql排序 前端不支持 故此新增此groovy实现 --内部接口
 * @author: chieh
 * @date 2023-01-30 12:30:00
 *
 */

public class aipHSTServiceTaskDeskList implements ServiceBackInvoke {
    private static final Log log = LogFactory.getLog(aipHSTServiceTaskDeskList.class);
    String custapiid = getProperty.get("custapiid");
    String custapikey = getProperty.get("custapikey");
    public String doCallBack(EliteBean eliteBean, HttpServletRequest request, HttpServletResponse response) {
        response.setCharacterEncoding("UTF-8");

        String getMethod = request.getMethod();

        log.debug("getMethod:" + getMethod);
        long endTime ;
        String inData = "";
        String outData = "";
        String dataSourceID = "dataSource";

        BufferedReader iny = null;
        if (getMethod.equals("GET")) {
            inData = request.getParameter("data");
            log.debug("原始Get请求参数:" + inData);
        } else {
            InputStreamReader reader = new InputStreamReader(request.getInputStream(), "UTF-8");
            iny = new BufferedReader(reader);
            String line;
            while ((line = iny.readLine()) != null) {
                inData += line;
            }
            log.debug("原始Post请求参数:" + inData);
        }

        try {
            long startTime = System.currentTimeMillis();

            // 调用处理信息
            JSONObject inObject = JSONObject.parseObject(inData);
            log.debug("处理前的Json：" + inObject.toString());

			//筛选条件
            def hst_no = inObject.getString("hst_no");//华盛号
            def SEARCH = inObject.getString("SEARCH");//客户名
            def customertype2_id = inObject.getString("customertype2_id");//客户电话
            def recommend = inObject.getString("recommend");//是否推荐 
            def rujin = inObject.getString("rujin");//是否首转入资产 
            def registerstatus = inObject.getString("registerstatus");//开户状态 
            def depositstatus = inObject.getString("depositstatus");//入金状态 
            def market_channel = inObject.getString("market_channel");//注册渠道
			//涉及多选
            def handlebygroup_id = inObject.getString("handlebygroup_id").replaceAll(",","','");//组
            def handleby_id = inObject.getString("handleby_id").replaceAll(",","','");//服务顾问
            def objectivesubstatus = inObject.getString("objectivesubstatus").replaceAll(",","','");//跟进一级
            def objectivesubstatus_id2 = inObject.getString("objectivesubstatus_id2").replaceAll(",","','");//跟进二级
            def obstatus = inObject.getString("obstatus").replaceAll(",","','");//外拨状态
            def b_information = inObject.getString("b_information").replaceAll(",","','");//B岗人员
            def wechat_add_status = inObject.getString("wechat_add_status").replaceAll(",","','");//微信添加状态
			//时间维度
            def createddate_start = inObject.getString("createddate_start");//下发时间 
            def createddate_end = inObject.getString("createddate_end");//下发时间 
			def kh_createddate_start = inObject.getString("kh_createddate_start");//开户时间 
            def kh_createddate_end = inObject.getString("kh_createddate_end");//开户时间 
			def registration_time = inObject.getString("registration_time");//注册时间 
            def registration_end = inObject.getString("registration_end");//注册时间 
			def kh_scratch_start = inObject.getString("kh_scratch_start");//筛选暂存时间起 
            def kh_scratch_end = inObject.getString("kh_scratch_end");//筛选暂存时间止  
			def kh_examine_start = inObject.getString("kh_examine_start");//审核时间起 
            def kh_examine_end = inObject.getString("kh_examine_end");//审核时间止 
			
			//left join 关联 
            def batchid_id = inObject.getString("batchid_id");//活动批次 
            def tab_activity_name = inObject.getString("tab_activity_name");//活动 
            def tab_activity_participation = inObject.getString("tab_activity_participation");//活动参与状态 
            def customerlabel = inObject.getString("customerlabel");//顾问标签
            def privatelabel = inObject.getString("privatelabel");//企微标签 
            def hst_screenshot = inObject.getString("hst_screenshot");//截图 
            def followuptype = inObject.getString("followuptype");//关联跟进历史最新一条 
            def daydifference = inObject.getString("daydifference");//关联跟进历史最新一条的间隔时间
			
			//1 name
            def ordername = inObject.getString("ordername");//自定义排序名称
			//4 order by
            def orderpriority = inObject.getString("orderpriority");//自定义排序优先级

			//limit
            def b = inObject.getIntValue("b");//分页初始
            def c = inObject.getIntValue("c");//分页结束

			log.debug("解析信息-" + "hst_no: " + hst_no
					+"\n"+ "SEARCH: " + SEARCH
					+"\n"+ "customertype2_id: " + customertype2_id
					+"\n"+ "recommend: " + recommend
					+"\n"+ "rujin: " + rujin
					+"\n"+ "registerstatus: " + registerstatus
					+"\n"+ "depositstatus: " + depositstatus
					+"\n"+ "market_channel: " + market_channel
					+"\n"+ "handlebygroup_id: " + handlebygroup_id
					+"\n"+ "handleby_id: " + handleby_id
					+"\n"+ "objectivesubstatus: " + objectivesubstatus
					+"\n"+ "objectivesubstatus_id2: " + objectivesubstatus_id2
					+"\n"+ "obstatus: " + obstatus
					+"\n"+ "b_information: " + b_information
					+"\n"+ "wechat_add_status: " + wechat_add_status
					+"\n"+ "createddate_start: " + createddate_start
					+"\n"+ "createddate_end: " + createddate_end
					+"\n"+ "kh_createddate_start: " + kh_createddate_start
					+"\n"+ "kh_createddate_end: " + kh_createddate_end
					+"\n"+ "registration_time: " + registration_time
					+"\n"+ "registration_end: " + registration_end
					+"\n"+ "kh_scratch_start: " + kh_scratch_start
					+"\n"+ "kh_scratch_end: " + kh_scratch_end
					+"\n"+ "kh_examine_start: " + kh_examine_start
					+"\n"+ "kh_examine_end: " + kh_examine_end
					+"\n"+ "batchid_id: " + batchid_id
					+"\n"+ "tab_activity_name: " + tab_activity_name
					+"\n"+ "tab_activity_participation: " + tab_activity_participation
					+"\n"+ "customerlabel: " + customerlabel
					+"\n"+ "privatelabel: " + privatelabel
					+"\n"+ "hst_screenshot: " + hst_screenshot
					+"\n"+ "followuptype: " + followuptype
					+"\n"+ "daydifference: " + daydifference
					+"\n"+ "ordername: " + ordername
					+"\n"+ "orderpriority: " + orderpriority
					+"\n"+ "b: " + b
					+"\n"+ "c: " + c);            // 初始化返回参数
            JSONObject result = new JSONObject();

            if (b>=0&&c>0){
                String q_NameSQL1=ordername;//-- 1 字段名称
                String q_LeftjoinSQL1="";//-- 2  left join 关联
                String q_WhereSQL1="";//-- 3 and 筛选条件
                String q_OrderbySQL1=orderpriority;//-- 4 order by 排序
				
                q_WhereSQL1=" and 1=1";
               
			   //3 筛选条件
			   if(hst_no!=null&&hst_no!=""){//华盛号 
				   q_WhereSQL1=q_WhereSQL1+" AND c.CUSTOMER_ID=trim('" + hst_no + "') ";
			   }
			   if(SEARCH!=null&&SEARCH!=""){//客户名 
				   q_WhereSQL1=q_WhereSQL1+" and (c.CUSTOMERNAME like '" + SEARCH + "' ";
			   }
			   if(customertype2_id!=null&&customertype2_id!=""){//客户电话 
				   q_WhereSQL1=q_WhereSQL1+" and c.mobile_enc='" + customertype2_id + "' ";
			   }
			   if(recommend!=null&&recommend!=""){//是否推荐  
				   q_WhereSQL1=q_WhereSQL1+" and c.sq_8='" + recommend + "'";
			   }
			   if(tab_activity_participation!=null&&tab_activity_participation!=""&&tab_activity_participation!='NO'){//活动参与状态   
				   q_WhereSQL1=q_WhereSQL1+" and ea.activity_state='" + tab_activity_participation + "' ";
			   }
			   if(tab_activity_participation!=null&&tab_activity_participation!=""&&tab_activity_participation=='NO'){//活动参与状态  
				   q_WhereSQL1=q_WhereSQL1+" and ea.activity_state is   NULL ";
			   }
			   if(market_channel!=null&&market_channel!=""){//注册渠道  
				   q_WhereSQL1=q_WhereSQL1+" AND c.sq_9 = '" + market_channel + "'";
			   }
			   if(rujin=="未转入"){//是否首转入资产  
				   q_WhereSQL1=q_WhereSQL1+" and c.firstAssetTime IS NULL";
			   }
			   if(rujin=="已转入"){//是否首转入资产  
				   q_WhereSQL1=q_WhereSQL1+" and c.firstAssetTime is not null";
			   }
			   if(registerstatus!=null&&registerstatus!=""){//开户状态  
				   q_WhereSQL1=q_WhereSQL1+" and c.registerstatus='" + registerstatus + "'";
			   }
			   if(depositstatus!=null&&depositstatus!=""){//开户状态  
				   q_WhereSQL1=q_WhereSQL1+" and c.depositstatus='" + depositstatus + "'";
			   }
			   if(hst_screenshot=="是"){//截图  
				   q_WhereSQL1=q_WhereSQL1+" and hstc.objective_guid is not null ";
			   }
			   if(followuptype=="1"){//1 当天跟进数  
				   q_WhereSQL1=q_WhereSQL1+" and hasn.advisercallnature_2 is not null and e.createddate>date_format(now()-interval " + daydifference + " day,'%Y-%m-%d 18:00:00')  and e.createddate<=date_format(now(),'%Y-%m-%d 17:59:59')  and hasn.createddate>=date_format(now()-interval " + daydifference + " day,'%Y-%m-%d 18:00:00')  and hasn.createddate<=date_format(now(),'%Y-%m-%d 17:59:59')";
			   }
			   if(followuptype=="2"){//2 当天未跟进数  
				   q_WhereSQL1=q_WhereSQL1+" and hasn.advisercallnature_2 is null  and e.createddate>=date_format(now()-interval " + daydifference + " day,'%Y-%m-%d 18:00:00')   and e.createddate<=date_format(now(),'%Y-%m-%d 17:59:59') ";
			   }
			   if(followuptype=="3"){//3 跟进用户数  
				   q_WhereSQL1=q_WhereSQL1+" AND hasn.advisercallnature_2 IS NOT NULL ";
			   }
			   if(followuptype=="4"){//4 未跟进用户数  
				   q_WhereSQL1=q_WhereSQL1+" AND hasn.advisercallnature_2 IS NULL  ";
			   }
			   
			   // 涉及多选的  replaceAll()
			   if(handlebygroup_id!=null&&handlebygroup_id!=""){//组 
				   q_WhereSQL1=q_WhereSQL1+" and e.HANDLEGROUP_ID   in  ('" + handlebygroup_id + "') ";
			   }
			   if(handleby_id!=null&&handleby_id!=""){//服务顾问
				   q_WhereSQL1=q_WhereSQL1+" and e.handleby_id   in ('" + handleby_id + "') ";
			   }
			   if(objectivesubstatus!=null&&objectivesubstatus!=""){//跟进一级  
				   q_WhereSQL1=q_WhereSQL1+" and e.objectivesubstatus in ('" + objectivesubstatus + "') ";
			   }
			   if(objectivesubstatus_id2!=null&&objectivesubstatus_id2!=""){//跟进二级  
				   q_WhereSQL1=q_WhereSQL1+" and ob.contact_cause in ('" + objectivesubstatus_id2 + "') ";
			   }
			   if(obstatus!=null&&obstatus!=""){//外拨状态  
				   q_WhereSQL1=q_WhereSQL1+" and e.elitetaskstatus  in ('" + obstatus + "') ";
			   }
			   if(b_information!=null&&b_information!=""){//B岗人员  
				   q_WhereSQL1=q_WhereSQL1+" and e.handleby_id  in('" + b_information + "') ";
			   }
			   if(wechat_add_status!=null&&wechat_add_status!=""){//微信添加状态  
				   q_WhereSQL1=q_WhereSQL1+" and c.WeChat_Add_Status  in ('" + wechat_add_status + "') ";
			   }
			   if(batchid_id!=null&&batchid_id!=""){//活动批次  
				   q_WhereSQL1=q_WhereSQL1+" and ea.batchid  in ('" + batchid_id + "') ";
			   }
			   if(tab_activity_name!=null&&tab_activity_name!=""){//活动  
				   q_WhereSQL1=q_WhereSQL1+" and ea.tab_activity_id   in  ('" + tab_activity_name + "') and  hab.state='1'";
			   }
			   if(customerlabel!=null&&customerlabel!=""&&privatelabel==""){//顾问标签  
				   q_WhereSQL1=q_WhereSQL1+" and r1.LABELID in ('" + customerlabel + "') ";
			   }
			   if(privatelabel!=null&&privatelabel!=""&&customerlabel==""){//顾问标签  
				   q_WhereSQL1=q_WhereSQL1+" and r1.LABELID in ('" + privatelabel + "') ";
			   }
			   if(customerlabel!=null&&customerlabel!=""&&privatelabel!=null&&privatelabel!=""){//顾问标签  
				   q_WhereSQL1=q_WhereSQL1+" and r1.LABELID in('" + privatelabel +"','"+customerlabel+ "') ";
			   }
			   
			   
			   //时间维度
			   if(createddate_start!=null&&createddate_start!=""){//下发时间  
				   q_WhereSQL1=q_WhereSQL1+" and e.createddate>=date_format('" + createddate_start + "','%Y-%m-%d 00:00:00') ";
			   }
			   if(createddate_end!=null&&createddate_end!=""){//下发时间  
				   q_WhereSQL1=q_WhereSQL1+" and e.createddate<=date_format('" + createddate_end + "','%Y-%m-%d 23:59:59') ";
			   }
			   if(kh_createddate_start!=null&&kh_createddate_start!=""){//开户时间  
				   q_WhereSQL1=q_WhereSQL1+" and c.STRINGFIELD8>=date_format('" + kh_createddate_start + "','%Y-%m-%d 00:00:00') ";
			   }
			   if(kh_createddate_end!=null&&kh_createddate_end!=""){//开户时间  
				   q_WhereSQL1=q_WhereSQL1+" and c.STRINGFIELD8<=date_format('" + kh_createddate_end + "','%Y-%m-%d 23:59:59') ";
			   }
			   if(registration_time!=null&&registration_time!=""){//注册时间  
				   q_WhereSQL1=q_WhereSQL1+" and c.STRINGFIELD7>=date_format('" + registration_time + "','%Y-%m-%d 00:00:00') ";
			   }
			   if(registration_end!=null&&registration_end!=""){//注册时间  
				   q_WhereSQL1=q_WhereSQL1+" and c.STRINGFIELD7<=date_format('" + registration_end + "','%Y-%m-%d 23:59:59') ";
			   }
			   if(kh_scratch_start!=null&&kh_scratch_start!=""){//筛选暂存时间起  
				   q_WhereSQL1=q_WhereSQL1+" and c.stashtime>=date_format('" + kh_scratch_start + "','%Y-%m-%d 00:00:00') ";
			   }
			   if(kh_scratch_end!=null&&kh_scratch_end!=""){//筛选暂存时间止  
				   q_WhereSQL1=q_WhereSQL1+" and c.stashtime<=date_format('" + kh_scratch_end + "','%Y-%m-%d 23:59:59') ";
			   }
			   if(kh_examine_start!=null&&kh_examine_start!=""){//筛选暂存时间起  
				   q_WhereSQL1=q_WhereSQL1+" and c.audittime>=date_format('" + kh_examine_start + "','%Y-%m-%d 00:00:00') ";
			   }
			   if(kh_examine_end!=null&&kh_examine_end!=""){//筛选暂存时间止  
				   q_WhereSQL1=q_WhereSQL1+" and c.audittime<=date_format('" + kh_examine_end + "','%Y-%m-%d 23:59:59') ";
			   }
			   
			   
			   //2 left join 关联 
			   if((batchid_id!=null&&batchid_id!="")||(tab_activity_name!=null&&tab_activity_name!="")||(tab_activity_participation!=null&&tab_activity_participation!="")){//活动left  
				   q_LeftjoinSQL1=q_LeftjoinSQL1+" LEFT JOIN elitetask_activity  ea on ea.objective_guid=e.objective_guid LEFT JOIN hst_activity_batch  hab on hab.batchid=ea.batchid  ";
			   }
			   if((customerlabel!=null&&customerlabel!="")||(privatelabel!=null&&privatelabel!="")){//标签left  
				   q_LeftjoinSQL1=q_LeftjoinSQL1+" left join (select clo.RELATION_GUID as b,LABELCOMMENT,clt.LABELID,qwtagId	,qwtype from customerlabeltype clt  left join  customerlabelo clo on clo.RELATION_LABELID=clt.LABELID    GROUP BY clt.LABELID,clo.RELATION_GUID )r1 on  e.OBJECTIVE_GUID=r1.b and r1.LABELCOMMENT is not null ";
			   }
			   if(hst_screenshot=="是"){//截图  
				   q_LeftjoinSQL1=q_LeftjoinSQL1+" left join hst_screenshot hstc on hstc.objective_guid=ob.objective_guid ";
			   }
			   if(followuptype!=null&&followuptype!=""&&followuptype!="2"){//关联跟进历史最新一条  
				   q_LeftjoinSQL1=q_LeftjoinSQL1+" left join ( select t.*,row_number() over(partition by t.customer_guid order by t.createddate desc) as rowNum  from hs_advisercallnature t ) hasn  ON hasn.customer_guid = e.customer_guid and  hasn.rowNum = 1  and hasn.objective_guid=e.objective_guid  and hasn.CREATEDBY=e.HANDLEBY_ID ";
			   }
			   if(followuptype!=null&&followuptype!=""&&followuptype=="2"){//关联跟进历史最新一条  
				   q_LeftjoinSQL1=q_LeftjoinSQL1+" left join( select t.*,row_number() over(partition by t.customer_guid order by t.createddate desc) as rowNum from hs_advisercallnature t  ) hasn  ON hasn.customer_guid = e.customer_guid and  hasn.rowNum = 1 and hasn.objective_guid=e.objective_guid and hasn.CREATEDBY=e.HANDLEBY_ID and hasn.createddate>=date_format(now()-interval " + daydifference + " day,'%Y-%m-%d 18:00:00')  and hasn.createddate<=date_format(now(),'%Y-%m-%d 17:59:59') ";
			   }

                //-- 数据信息
               //String queryOrderSQL1 = "SELECT DISTINCT 0, e.elitetask_GUID, e.Objective_GUID, case when c.customernameabb is null or c.customernameabb='' then c.customername else c.customernameabb end,e.MAP_ID,cast(e.MODIFIEDDATE as CHAR), sob.OBSTATUSDESCRIPTION,e.Comments ,e.Attempt, e.Max_Attempt,  e.laststatus as callresultreason_id,e.CUSTOMER_GUID,c.CUSTOMER_ID as 华盛号,c.mobile as 电话,c.stringfield5 as 客户状态,case c.sq_8 when '1' then '是' when '2' then '否' end as 是否推荐,cast(c.STRINGFIELD8  as CHAR) as 开户时间,st.STAFFNAME as 服务顾问,ca.AGENTSTATUSDESCRIPTION as 服务顾问状态,stf.STAFFNAME as 更新人,cast(e.Createddate as CHAR) as 更新时间,so.OBJECTIVESUBSTATUS_DESC as 跟进结果,cast(e.createddate as CHAR) as 下发时间,ob.objective_guid,ob.Allocation_pool as 是否分配,ob.appointment,case c.WeChat_Add_Status when '0' then '未添加' when '1' then '已发送' when '2' then '已添加' end as 是否添加微信,c.vip as vip等级,c.sq_2 as 账户类型,c.STRINGFIELD6 as 证件签发地,c.STRINGFIELD7 as 渠道号,c.STRINGFIELD8 as 客户来源,c.STRINGFIELD3 as 户口,case c.gender when '1' then '男' when '2' then '女' end as 性别,c.stringfield13 as 渠道,cast(c.stringfield7 as CHAR) as 注册时间,case when c.firstAssetTime is null then '未转入' else '已转入'  end 首入资产状态,c.Sq_6 as 邀请人 ,c.Sq_5 as 邀请人华盛号 ,c.Sq_7 as 邀请人类型,ob.comments,c.sq_9,ct.Code_Name,st.staff_id,c.STRINGFIELD17,ct2.Code_Name as 开户状态,Invitee_count 被邀请人数量,cast(c.stashtime as CHAR),c.audittime,so2.Code_Name as 跟进二级,ct3.Code_Name,"+q_NameSQL1+" as 优先跟进排序, case when c.depositStatus='3' then '已入金' when c.depositStatus='5' then '入金撤销申请' when c.depositStatus='4' then '入金已驳回' when c.depositStatus='2' then '入金处理中' when c.depositStatus='1' then '入金待审核' when c.registerstatus='1' then '已开户' when c.registerstatus='4' then '开户审核中' when c.registerstatus='3' then '开户暂存' when c.registerstatus='5' then '开户暂存申请' when c.registerstatus='2' then '注册' else '' end as 用户状态, case  when c.depositStatus is not  null then cast(c.depositUpdateTime as CHAR) when c.registerstatus='1' then cast(c.STRINGFIELD8 as CHAR) when c.registerstatus='4' then cast(c.audittime as CHAR) when c.registerstatus='3' then  cast(c.stashtime as CHAR) when c.registerstatus='5' then '' when c.registerstatus='2' then cast(c.STRINGFIELD7 as CHAR) else '' end as 状态更新时间, case c.latestbehaviortype  when '1' then '在线网聊' when '2' then '呼入接通' when '3' then '呼出接通' when '4' then '企微回复' when '5' then '企微添加通过' else '' end  as 用户最新行为, cast(c.latestbehaviortime as CHAR) as 用户最新行为时间, case when c.firstAssetChannel  is not null and c.firstAssetChannel!='' then c.firstAssetChannel when c.openingChannelName is not null and c.openingChannelName!='' then c.openingChannelName when c.STRINGFIELD13 is not null and c.STRINGFIELD13!='' then c.STRINGFIELD13 end as 最新渠道, case when c.firstAssetChannelId is not null and c.firstAssetChannelId!=''  then c.firstAssetChannelId when c.openingChannelId is not null and c.openingChannelId!=''  then c.openingChannelId when c.sq_9 is not null and c.sq_9!=''  then c.sq_9 end as 最新渠道号, cast(ob.distributiontime as CHAR) as 分配时间 FROM elitetask e left join customer c on e.customer_guid=c.customer_guid LEFT JOIN objective ob on e.OBJECTIVE_GUID=ob.OBJECTIVE_GUID  and ob.MAP_ID='MY48ZX' LEFT JOIN staff st on e.HANDLEBY_ID=st.STAFF_ID LEFT JOIN c_codetables ct on c.sq_9=ct.Code_id and ct.use_type='渠道类型' left join C_AgentStatus ca on ca.agentstatus_id=case when st.WORKSTATUS='QUIT' then 'QUIT' when st.starttime<NOW() and st.endtime>NOW() then 'ILL' else st.WORKSTATUS end left join sysc_objectivesubstatus so on ob.objectivesubstatus=so.OBJECTIVESUBSTATUS_ID left join c_codetables so2 on ob.contact_cause=so2.Code_ID and so2.Use_Type='外呼结束码二级'  left join sysc_obstatus sob on e.elitetaskstatus=sob.OBSTATUS left join staff stf on e.Createdby=stf.STAFF_ID LEFT JOIN c_codetables ct2 on c.registerstatus=ct2.Code_id and ct2.use_type='开户状态' LEFT JOIN c_codetables ct3 on c.depositstatus=ct3.Code_id and ct3.use_type='入金状态' "+q_LeftjoinSQL1+" where 1=1   and e.MAP_ID='MY48ZX' "+q_WhereSQL1 +"	"+ q_OrderbySQL1+" LIMIT "+b+","+c+" ";
               String queryOrderSQL1 = "SELECT  0 as a, e.elitetask_GUID, e.Objective_GUID, case when c.customernameabb is null or c.customernameabb='' then c.customername else c.customernameabb end as customernameabb,e.MAP_ID,cast(e.MODIFIEDDATE as CHAR) as MODIFIEDDATE, sob.OBSTATUSDESCRIPTION,e.Comments ,e.Attempt, e.Max_Attempt,  e.laststatus as callresultreason_id,e.CUSTOMER_GUID,c.CUSTOMER_ID as CUSTOMER_ID,c.mobile_enc as mobile,c.stringfield5 as stringfield5,case c.sq_8 when '1' then 'yes' when '2' then 'no' end as b,cast(c.STRINGFIELD8  as CHAR) as STRINGFIELD8,st.STAFFNAME as STAFFNAME,ca.AGENTSTATUSDESCRIPTION as AGENTSTATUSDESCRIPTION,stf.STAFFNAME as t,cast(e.Createddate as CHAR) as Createddate,so.OBJECTIVESUBSTATUS_DESC as OBJECTIVESUBSTATUS_DESC,cast(e.createddate as CHAR) as r,ob.objective_guid as s,ob.Allocation_pool as Allocation_pool,ob.appointment,case c.WeChat_Add_Status when '0' then 'Not added' when '1' then 'been sent' when '2' then 'Added' end as c,c.vip as vip,c.sq_2 as sq_2,c.STRINGFIELD6 as STRINGFIELD6,c.STRINGFIELD7 as STRINGFIELD7,c.STRINGFIELD8 as n,c.STRINGFIELD3 as STRINGFIELD3,case c.gender when '1' then 'male' when '2' then 'female' end as gender,c.stringfield13 as stringfield13,cast(c.stringfield7 as CHAR) as q,case when c.firstAssetTime is null then 'Not transferred in' else 'Transferred in'  end as d,c.Sq_6 as Sq_6 ,c.Sq_5 as Sq_5 ,c.Sq_7 as Sq_7,ob.comments as u,c.sq_9,ct.Code_Name,st.staff_id,c.STRINGFIELD17,ct2.Code_Name as f,Invitee_count,cast(c.stashtime as CHAR) as stashtime,cast(c.audittime as CHAR) as audittime,so2.Code_Name as g,ct3.Code_Name as o, "+q_NameSQL1+" as h, case when c.registerstatus='6' then 'Cancelled' when  c.depositStatus='3' then 'Already deposited' when c.depositStatus='5' then 'Withdrawal application for deposit' when c.depositStatus='4' then 'Deposit rejected' when c.depositStatus='2' then 'Deposit processing in progress' when c.depositStatus='1' then 'Deposit pending review' when c.registerstatus='1' then 'Already open account' when c.registerstatus='4' then 'Account opening review in progress' when c.registerstatus='3' then 'Account opening temporary storage' when c.registerstatus='5' then 'Account opening temporary storage application' when c.registerstatus='2' then 'register' else '' end as i, case  when c.depositStatus is not  null then cast(c.depositUpdateTime as CHAR) when c.registerstatus='1' then cast(c.STRINGFIELD8 as CHAR) when c.registerstatus='4' then cast(c.audittime as CHAR) when c.registerstatus='3' then  cast(c.stashtime as CHAR) when c.registerstatus='5' then '' when c.registerstatus='2' then cast(c.STRINGFIELD7 as CHAR) else '' end as j, case c.latestbehaviortype  when '1' then 'Online chat' when '2' then 'Incoming call connection' when '3' then '' when '4' then 'Enterprise WeChat reply' when '5' then 'Enterprise WeChat added through' else '' end  as k, cast(c.latestbehaviortime as CHAR) as latestbehaviortime, case when c.firstAssetChannel  is not null and c.firstAssetChannel!='' then c.firstAssetChannel when c.openingChannelName is not null and c.openingChannelName!='' then c.openingChannelName when c.STRINGFIELD13 is not null and c.STRINGFIELD13!='' then c.STRINGFIELD13 end as l, case when c.firstAssetChannelId is not null and c.firstAssetChannelId!=''  then c.firstAssetChannelId when c.openingChannelId is not null and c.openingChannelId!=''  then c.openingChannelId when c.sq_9 is not null and c.sq_9!=''  then c.sq_9 end as m, cast(ob.distributiontime as CHAR) as distributiontime FROM elitetask e left join customer c on e.customer_guid=c.customer_guid LEFT JOIN objective ob on e.OBJECTIVE_GUID=ob.OBJECTIVE_GUID  and ob.MAP_ID='MY48ZX' LEFT JOIN staff st on e.HANDLEBY_ID=st.STAFF_ID LEFT JOIN c_codetables ct on c.sq_9=ct.Code_id and ct.use_type='渠道类型' left join C_AgentStatus ca on ca.agentstatus_id=case when st.WORKSTATUS='QUIT' then 'QUIT' when st.starttime<NOW() and st.endtime>NOW() then 'ILL' else st.WORKSTATUS end left join sysc_objectivesubstatus so on ob.objectivesubstatus=so.OBJECTIVESUBSTATUS_ID left join c_codetables so2 on ob.contact_cause=so2.Code_ID and so2.Use_Type='外呼结束码二级'  left join sysc_obstatus sob on e.elitetaskstatus=sob.OBSTATUS left join staff stf on e.Createdby=stf.STAFF_ID LEFT JOIN c_codetables ct2 on c.registerstatus=ct2.Code_id and ct2.use_type='开户状态' LEFT JOIN c_codetables ct3 on c.depositstatus=ct3.Code_id and ct3.use_type='入金状态' "+q_LeftjoinSQL1+" where 1=1   and e.MAP_ID='MY48ZX' "+q_WhereSQL1 +"	"+ q_OrderbySQL1+" LIMIT "+b+","+c+" ";
                log.debug("执行顾问任务台查询数据SQL:" + queryOrderSQL1);

                List OrderList1 = eliteBean.chaxun_sql(queryOrderSQL1, "dataSource");
                //获取总行数
                log.debug("执行顾问任务台查询数据SQL获取总条数:" + OrderList1.size());
                String reJoson1 = Util.getJsonFromList(OrderList1);
                JSONArray dataArr1 = JSONArray.parseArray(reJoson1);
                result.put("followupdata", dataArr1);

                //log.debug("result:" + result);

                endTime = System.currentTimeMillis();
                outData="{\"resultcode\":\"1\",\"resultmessage\":\"处理成功,调用问题处理时长(毫秒):" + (endTime - startTime) + "\",\"data\":"+result.toString()+"}";
            }else{
                log.error("处理失败(customerid必填)!");
                endTime = System.currentTimeMillis();
                return "{\"resultcode\":\"0\",\"resultmessage\":\"处理失败(必填),调用问题处理时长(毫秒):" + (endTime - startTime) + "\",\"data\":[]}";
            }

        } catch (Exception e) {
            log.error("接口处理异常" + e.getMessage());
            outData = "{\"resultcode\":\"0\",\"resultmessage\":\"处理失败(" + e.getMessage() + ")\",\"data\":[]}";
        }

        //log.debug("开户(入金)客户信息完成返回结果:" + outData);

        return outData;
    }

}
